package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpStockInCheckoutEntity;
import com.b2c.entity.erp.enums.InvoiceTransTypeEnum;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.ErpStockInCheckoutFormAddVo;
import com.b2c.entity.vo.ErpStockInCheckoutFormDetailVo;
import com.b2c.entity.vo.ErpStockInCheckoutItemVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 *
 * @author qlp
 * @date 2019-09-29 11:04
 */
@Repository
public class ErpStockInCheckoutRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加入库检验表单
     *
     * @param addVo
     * @return
     */
    @Transactional
    public Long addCheckoutForm(ErpStockInCheckoutFormAddVo addVo) {
        Long totalQuantity = 0l;//验货总数量
        for (var item : addVo.getItems()) {
            totalQuantity += item.getQuantity();
        }

        /********1、添加检验单**********/
        StringBuilder insert = new StringBuilder();
        insert.append("INSERT INTO ").append(Tables.ErpStockInCheckout)
                .append(" (number,transType,transTypeName,remark,isDelete,checkoutUserId,checkoutUserName,invoiceNo,invoiceId,stockInStatus," +
                        "createTime,hasQcReport,qcInspector,quantity,inQuantity,createOn)")
                .append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Long finalTotalQuantity = totalQuantity;
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(insert.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, addVo.getNumber());
            ps.setString(2, InvoiceTransTypeEnum.Purchase.getIndex());
            ps.setString(3, InvoiceTransTypeEnum.Purchase.getName());
            ps.setString(4, "");
            ps.setInt(5, 0);
            ps.setInt(6, addVo.getCheckoutUserId());
            ps.setString(7, addVo.getCheckoutUserName());
            ps.setString(8, addVo.getInvoiceNo());
            ps.setLong(9, addVo.getInvoiceId());
            ps.setInt(10, 0);
            ps.setObject(11, new java.util.Date());
            ps.setInt(12, addVo.getHasQcReport());
            ps.setString(13, addVo.getQcInspector());
            ps.setLong(14, finalTotalQuantity);
            ps.setLong(15, 0);
            ps.setLong(16, System.currentTimeMillis() / 1000);

            return ps;
        }, keyHolder);

        Long checkoutId = keyHolder.getKey().longValue();

        /**********2、添加验货单明细***********/
        for (var item : addVo.getItems()) {

            String sql = "INSERT INTO " + Tables.ErpStockInCheckoutItem + " (checkoutId,goodsId,specId,specNumber,quantity,inQuantity) VALUE (?,?,?,?,?,?)";
            jdbcTemplate.update(sql, checkoutId, item.getGoodsId(), item.getSpecId(), item.getSpecNumber(), item.getQuantity(), 0);

            /************3.0、更新invoice_info 表 已入库数量 **************/
            jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET inQuantity=inQuantity+? WHERE id=?", item.getQuantity(), item.getInvoiceInfoId());


        }


        /************3.1、更新invoice 表 已入库数量 **************/
        jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET inQuantity=inQuantity+? WHERE id=?", totalQuantity, addVo.getInvoiceId());
        return checkoutId;
    }

    /**
     * 根据单据id 获取验货数据
     *
     * @param invoiceId
     * @return
     */
    public List<ErpStockInCheckoutEntity> getCheckoutForm(Long invoiceId) {
        return jdbcTemplate.query("SELECT * FROM " + Tables.ErpStockInCheckout + " WHERE invoiceId=?", new BeanPropertyRowMapper<>(ErpStockInCheckoutEntity.class), invoiceId);
    }

    /**
     * 获取验货list
     *
     * @param pageIndex 页码
     * @param pageSize
     * @param number    单据编号
     * @param status    状态
     * @return
     */
    public PagingResponse<ErpStockInCheckoutEntity> getCheckoutList(Integer pageIndex, Integer pageSize, String number, Integer status) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS i.* " +
                " FROM " + Tables.ErpStockInCheckout + " as i " +
                " WHERE 1=1 ";

        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(number)==false) {
            sql += " AND number=? ";
            params.add(number);
        }
        if (status != null) {
            sql += " AND stockInStatus=? ";
            params.add(status);
        }

        sql += " ORDER BY i.stockInStatus asc,i.id DESC LIMIT ?,?";

        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpStockInCheckoutEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInCheckoutEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 获取验货单详细
     * @param id
     * @return
     */
    public ErpStockInCheckoutFormDetailVo getDetailById(Long id){
        try {
            String sql = "SELECT i.*,(SELECT SUM(quantity) FROM "+Tables.ErpStockInCheckoutItem+" WHERE checkoutId=i.id) AS totalQuantity FROM " + Tables.ErpStockInCheckout + " i WHERE i.id=?";
            ErpStockInCheckoutFormDetailVo detailVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpStockInCheckoutFormDetailVo.class), id);

            String itemSQL = "SELECT i.*,gs.color_value,gs.size_value,g.name as goodsName FROM "+Tables.ErpStockInCheckoutItem +" i " +
                    " LEFT JOIN "+Tables.ErpGoodsSpec +" gs on gs.id = i.specId "+
                    " LEFT JOIN "+Tables.ErpGoods +" g on g.id = i.goodsId "+
                    " WHERE i.checkoutId=?";


            detailVo.setItems(jdbcTemplate.query(itemSQL,new BeanPropertyRowMapper<>(ErpStockInCheckoutItemVo.class),id));
            return detailVo;
        }catch (Exception e){
            return null;
        }
    }
}
